package cn.ainit.cw.cwcost;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.ainit.butn.proceduce.CommonProcedure;
import cn.ainit.common.ReturnObject;
import cn.ainit.sys.common.dao.BusinessBaseDao;

@Transactional
@Repository
public class CwCostDaoImpl extends BusinessBaseDao implements CwCostDao{
	@Override
	public boolean saveFinCost(CwCosts finCost) throws Exception {
		try {
			this.getSessionFactory().getCurrentSession().save(finCost);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}
		return true;
	}
	@Override
	public CwCosts toUpdateCost(String costId) throws Exception {
		return (CwCosts) this.getSessionFactory().getCurrentSession().get(CwCosts.class, costId);
	}
	@Override
	public boolean updApply(String sql) throws Exception {
		return this.executeSQL(sql);
	}
	@Override
	public boolean updateCost(CwCosts finCosts) throws Exception {
		this.getSessionFactory().getCurrentSession().update(finCosts);
		return true;
	}
	@Override
	public boolean execFinCost(String costId, String costType, String djCode)
			throws Exception {
		Connection conn=null;
		CallableStatement cs=null;
		try {
			 conn= this.getJdbcTemplate().getDataSource().getConnection();
			 String sql = "{Call pk_com_approve.up_GetDjApprove(?,?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,costId);
			 cs.setString(2,costType);
			 cs.setString(3,djCode);
			 cs.execute();
			 return true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return false;
	}
	@Override
	public String varFinCost(String sql) throws Exception {
		return this.getPkStr(sql);
	}
	@Override
	public int execFinCostInt(String sql) throws Exception {
		return this.getJdbcTemplate().queryForInt(sql);
	}
	@Override
	public String newFY(String costId,String manId) throws Exception {
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_CW_COST.up_CostToPayFY(?,?,?)}";//存储过程的名称（路径）PK_BJ_XT.up_ChToLx(?,?)
			 cs = conn.prepareCall(sql);
			 cs.setString(1,costId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,Types.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
		} catch (Exception e) {
			putValue = "fail";
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return putValue;
	}
	
	@Override
	public ReturnObject newPay(String costId,String manId) throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_CW_COST.up_costToPay(?,?,?)}";//存储过程的名称（路径）PK_BJ_XT.up_ChToLx(?,?)
			 System.out.println("付款单------"+sql);
			 cs = conn.prepareCall(sql);
			 cs.setString(1,costId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,Types.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
			 cs.execute("commit");
			 conn.setAutoCommit(true);
			 returnObject.setMsg(putValue);
			 returnObject.setInfo("success");
		}catch (SQLException se) {
			 String sbmsgs= se.getMessage();
	         String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
	         if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
//	        	 String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
//	        	 //执行存储过程，传入dsId和(exproc违反唯一约束的列名)
//	        	 String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
//	        	 cs = conn.prepareCall(errSql);  
//	        	 cs.registerOutParameter(1, Types.VARCHAR);  
//	        	 cs.setString(2,exproc);
//	        	 cs.setString(3,buParams.getDsid());
//	        	 cs.execute();
//	        	 putValue= cs.getString(1);
	         }else if(se.getErrorCode()== 20001){
	        	 putValue=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
	         }else{
	        	 putValue=sbmsgs;
	         }
	         returnObject.setInfo("fail");
	         returnObject.setMsg(putValue);
		}catch (Exception e) {
//			e.printStackTrace();
			putValue =e.getMessage();
			returnObject.setInfo("fail");
	        returnObject.setMsg(putValue);
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return returnObject;
	}
	@Override
	public ReturnObject bxfyNewPay(String costId, String manId)
			throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_CW_COST.up_bxToJkPay(?,?,?)}";//存储过程的名称（路径）PK_BJ_XT.up_ChToLx(?,?)
			 System.out.println("报销费用单生成借款付款单------"+sql);
			 cs = conn.prepareCall(sql);
			 cs.setString(1,costId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,Types.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
			 cs.execute("commit");
			 conn.setAutoCommit(true);
			 returnObject.setMsg(putValue);
			 returnObject.setInfo("success");
		}catch (SQLException se) {
			 String sbmsgs= se.getMessage();
	         String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
	         if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程

	         }else if(se.getErrorCode()== 20001){
	        	 putValue=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
	         }else{
	        	 putValue=sbmsgs;
	         }
	         returnObject.setInfo("fail");
	         returnObject.setMsg(putValue);
		}catch (Exception e) {
//			e.printStackTrace();
			putValue =e.getMessage();
			returnObject.setInfo("fail");
	        returnObject.setMsg(putValue);
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return returnObject;
	}

	@Override
	public ReturnObject bxfyNewCZ(String costId, String manId) throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_CW_COST.up_bxTobxPay(?,?,?)}";//存储过程的名称（路径）PK_BJ_XT.up_ChToLx(?,?)
			 System.out.println("报销费用单生成报销付款单------"+sql);
			 cs = conn.prepareCall(sql);
			 cs.setString(1,costId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,Types.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
			 cs.execute("commit");
			 conn.setAutoCommit(true);
			 returnObject.setMsg(putValue);
			 returnObject.setInfo("success");
		}catch (SQLException se) {
			 String sbmsgs= se.getMessage();
	         String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
	         if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程

	         }else if(se.getErrorCode()== 20001){
	        	 putValue=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
	         }else{
	        	 putValue=sbmsgs;
	         }
	         returnObject.setInfo("fail");
	         returnObject.setMsg(putValue);
		}catch (Exception e) {
			putValue =e.getMessage();
			returnObject.setInfo("fail");
	        returnObject.setMsg(putValue);
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return returnObject;
	}
	@Override
	public ReturnObject bxfyNewJKHK(String costId, String manId)
			throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_CW_COST.up_bxTojkhkPay(?,?,?)}";//存储过程的名称（路径）PK_BJ_XT.up_ChToLx(?,?)
			 System.out.println("报销费用单生成报销还款单------"+sql);
			 cs = conn.prepareCall(sql);
			 cs.setString(1,costId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,Types.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
			 cs.execute("commit");
			 conn.setAutoCommit(true);
			 returnObject.setMsg(putValue);
			 returnObject.setInfo("success");
		}catch (SQLException se) {
			 String sbmsgs= se.getMessage();
	         String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
	         if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程

	         }else if(se.getErrorCode()== 20001){
	        	 putValue=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
	         }else{
	        	 putValue=sbmsgs;
	         }
	         returnObject.setInfo("fail");
	         returnObject.setMsg(putValue);
		}catch (Exception e) {
			putValue =e.getMessage();
			returnObject.setInfo("fail");
	        returnObject.setMsg(putValue);
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return returnObject;
	}
	@Override
	public ReturnObject jsNewKPFunc(String jsId, String manId) throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_FX_DZ.up_jsToKP(?,?,?)}"; 
			 System.out.println("开票单------"+sql);
			 cs = conn.prepareCall(sql);
			 cs.setString(1,jsId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,Types.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
			 cs.execute("commit");
			 conn.setAutoCommit(true);
			 returnObject.setMsg(putValue);
			 returnObject.setInfo("success");
		}catch (SQLException se) {
			 String sbmsgs= se.getMessage();
	         String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
	         if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程

	         }else if(se.getErrorCode()== 20001){
	        	 putValue=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
	         }else{
	        	 putValue=sbmsgs;
	         }
	         returnObject.setInfo("fail");
	         returnObject.setMsg(putValue);
		}catch (Exception e) {
//			e.printStackTrace();
			putValue =e.getMessage();
			returnObject.setInfo("fail");
	        returnObject.setMsg(putValue);
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return returnObject;
	}
	@Override
	public ReturnObject kpNewSKFunc(String kpId, String manId) throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_FX.UP_KPTOSK(?,?,?)}"; 
			 System.out.println("开票单生成收款单------"+sql);
			 cs = conn.prepareCall(sql);
			 cs.setString(1,kpId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,Types.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
			 cs.execute("commit");
			 conn.setAutoCommit(true);
			 returnObject.setMsg(putValue);
			 returnObject.setInfo("success");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnObject;
	}
	@Override
	public ReturnObject newYF(String costId, String manId) throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_CW_COST.up_costToYfPay(?,?,?)}";//存储过程的名称（路径）PK_BJ_XT.up_ChToLx(?,?)
			 System.out.println("预付单------"+sql);
			 cs = conn.prepareCall(sql);
			 cs.setString(1,costId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,Types.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
			 cs.execute("commit");
			 conn.setAutoCommit(true);
			 returnObject.setMsg(putValue);
			 returnObject.setInfo("success");
		}catch (SQLException se) {
			 String sbmsgs= se.getMessage();
	         String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
	         if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
//	        	 String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
//	        	 //执行存储过程，传入dsId和(exproc违反唯一约束的列名)
//	        	 String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
//	        	 cs = conn.prepareCall(errSql);  
//	        	 cs.registerOutParameter(1, Types.VARCHAR);  
//	        	 cs.setString(2,exproc);
//	        	 cs.setString(3,buParams.getDsid());
//	        	 cs.execute();
//	        	 putValue= cs.getString(1);
	         }else if(se.getErrorCode()== 20001){
	        	 putValue=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
	         }else{
	        	 putValue=sbmsgs;
	         }
	         returnObject.setInfo("fail");
	         returnObject.setMsg(putValue);
		}catch (Exception e) {
//			e.printStackTrace();
			putValue =e.getMessage();
			returnObject.setInfo("fail");
	        returnObject.setMsg(putValue);
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return returnObject;
	}
	@Override
	public ReturnObject newHK(String costId, String manId) throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_CW_COST.up_costToHkPay(?,?,?)}";//存储过程的名称（路径）PK_BJ_XT.up_ChToLx(?,?)
			 System.out.println("还款单------"+sql);
			 cs = conn.prepareCall(sql);
			 cs.setString(1,costId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,Types.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
			 cs.execute("commit");
			 conn.setAutoCommit(true);
			 returnObject.setMsg(putValue);
			 returnObject.setInfo("success");
		}catch (SQLException se) {
			 String sbmsgs= se.getMessage();
	         String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
	         if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
//	        	 String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
//	        	 //执行存储过程，传入dsId和(exproc违反唯一约束的列名)
//	        	 String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
//	        	 cs = conn.prepareCall(errSql);  
//	        	 cs.registerOutParameter(1, Types.VARCHAR);  
//	        	 cs.setString(2,exproc);
//	        	 cs.setString(3,buParams.getDsid());
//	        	 cs.execute();
//	        	 putValue= cs.getString(1);
	         }else if(se.getErrorCode()== 20001){
	        	 putValue=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
	         }else{
	        	 putValue=sbmsgs;
	         }
	         returnObject.setInfo("fail");
	         returnObject.setMsg(putValue);
		}catch (Exception e) {
//			e.printStackTrace();
			putValue =e.getMessage();
			returnObject.setInfo("fail");
	        returnObject.setMsg(putValue);
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return returnObject;
	}
	@Override
	public List execSql(String sql) throws Exception {
		List list = new ArrayList();
		try {
			list = this.getJdbcTemplate().queryForList(sql);
			if (list != null) {
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public Integer qrtx(List costIds) throws Exception {
		Integer count=0;
		for(int i=0;i<costIds.size();i++){
			String sql="UPDATE cw_cost s SET s.is_xt_cost='cbHsToXT.001' WHERE s.cost_id='"+costIds.get(i)+"'";
			count+=this.jdbcTemplate.update(sql);
		}
		return count;
	}

}
